import os

import xlrd

from order import Item
from typing import List, Tuple

from params import Piece


def get_ord(string):
    value = ord(string)
    # 在ASCII码表中，大小写字母的十进制编码相差32
    if 65 <= value <= 90:
        value += 32
    return value

#判断字符串是不是相等，忽略大小写
def same_string(str1, str2):
    if not isinstance(str1, str) or not isinstance(str2, str):
        return False

    if len(str1) != len(str2):
        return False

    for index, item in enumerate(str1):
        value1 = get_ord(item)
        value2 = get_ord(str2[index])

        if value1 != value2:
            return False
    return True
# import pymysql
# def load_pieces_from_excels1(mat: str = None, files: List[str] = None) -> List[Item]:
#     mat_names = set()
#     items = []
#     cols = '优化批次号,部件条码,材料描述,开料长,开料宽,部件数量,超产,纹理,部件名称,部件规格,材料编码,备注,订单号,批次号,配套批号,分单号,开槽线,异形,CNC加工代码,CNC反面加工代码,套数,第几套,预包装信息,总包数,第几包,第几层,单元序号,单元名称,单元规格'.split(',')
#     i = 0
#     for file_name in files:
#         if not file_name.endswith('.csv'):
#             continue
#         with open(file_name) as f:
#             counter = 0
#             for r in f:
#                 if counter == 0:
#                     counter += 1
#                     continue
#                 if counter == 1:
#                     counter += 1
#                 if counter == 2 or counter == 3:
#                     counter += 1
#                     continue
#
#                 i += 1
#                 vals = r.split(',')[:29]
#                 mat_names.add(vals[2])
#                 if (mat and vals[2] == mat) or mat is None:
#                     details = dict(zip(cols, vals))
#                     details['id'] = i
#                     items.append(Item(int(max(vals[3], vals[4])), int(min(vals[3], vals[4])), oid=vals[12], details=r))
#     return items, mat_names

def load_pieces_from_excels1(mat: str = None, files: List[str] = None) -> List[Item]:
    mat_names = set()
    items = []
    cols = '优化批次号,部件条码,材料描述,开料长,开料宽,部件数量,超产,纹理,部件名称,部件规格,材料编码,备注,订单号,批次号,配套批号,分单号,开槽线,异形,CNC加工代码,CNC反面加工代码,套数,第几套,预包装信息,总包数,第几包,第几层,单元序号,单元名称,单元规格'.split(',')
    i = 0
    for file_name in files:
        if not file_name.endswith('.csv'):
            continue
        with open(file_name) as f:
            counter = 0
            for r in f:
                if counter == 0:
                    counter += 1
                    continue
                if counter == 1:
                    counter += 1
                if counter == 2 or counter == 3:
                    counter += 1
                    continue
                counter+=1

                i += 1
                vals = r.split(',')[:29]
                mat_names.add(vals[2])
                if (mat and vals[2] == mat) or mat is None:
                    details = dict(zip(cols, vals))
                    details['id'] = i
                    items.append(Item(int(max(vals[3], vals[4])), int(min(vals[3], vals[4])), oid=vals[12], details=r))
            print('文件{0}一共有{1}个item'.format(file_name,counter-3))
    return items, mat_names
def load_pieces_from_flask( searchlists: List[Piece]):
    mat_names = set()
    depart_num = set()
    optimization_type = set()
    datatime = set()
    items = []
    dic={}
    # cols = 'id,depart_num,dailyplan,production_type,material_type,material_no,order_number,optimization_type,length,width,texture,process,section_name,tricks,work_order,quantity,bar_code,datatime'.split(',')
    i = 0
    for row in searchlists:
        # print(file_name)
        optimization_type.add(row.optimization_type)
        datatime.add(row.datatime)
        depart_num.add(row.depart_num)
        mat_names.add(row.material_type)
        dic.setdefault(row.material_type, []).append(Item(row.length, row.width, oid=row.work_order, details=row))
        items.append(Item(row.length, row.width, oid=row.work_order, details=row))
    print(optimization_type)
    print(datatime)
    print(depart_num)
    return items, mat_names,dic,depart_num,optimization_type,datatime


def load_pieces_from_excels(files):
    print(files)
    mat_names = set()
    items = []
    dic={}
    cols = 'id,depart_num,dailyplan,production_type,material_type,material_no,order_number,optimization_type,length,width,texture,process,section_name,tricks,work_order,quantity,bar_code,datatime'.split(',')
    i = 0
    for file_name in files:
        if not file_name.endswith('.csv'):
            continue
        with open(file_name) as f:
            counter = 0
            for r in f:
                if counter == 0:
                    counter += 1
                    continue
                if counter == 1:
                    counter += 1
                if counter == 2 or counter == 3:
                    counter += 1
                    continue
                counter += 1
                i += 1
                vals = r.split(',')[:29]
                mat_names.add(vals[2])
                details = dict(zip(cols, vals))
                # details['id'] = i
                dic.setdefault(vals[2], []).append(Item(int(vals[3]), int(vals[4]), oid=vals[12], details=r))
                items.append(Item(int(vals[3]), int(vals[4]), oid=vals[12], details=r))

    return items, mat_names,dic

# def load_pieces_from_excels(mat: str = None, files: List[str] = None) -> List[Item]:
#
#     mat_names = set()
#     items = []
#     cols = '优化批次号,部件条码,材料描述,开料长,开料宽,部件数量,超产,纹理,部件名称,部件规格,材料编码,备注,订单号,批次号,配套批号,分单号,开槽线,异形,CNC加工代码,CNC反面加工代码,套数,第几套,预包装信息,总包数,第几包,第几层,单元序号,单元名称,单元规格'.split(',')
#     i = 0
#     for file_name in files:
#         if not file_name.endswith('.csv'):
#             continue
#         with open(file_name) as f:
#             counter = 0
#             for r in f:
#                 if counter == 0:
#                     counter += 1
#                     continue
#                 if counter == 1:
#                     counter += 1
#                 if counter == 2 or counter == 3:
#                     counter += 1
#                     continue
#                 counter += 1
#                 i += 1
#                 vals = r.split(',')[:29]
#                 mat_names.add(vals[2])
#                 result=same_string(mat,vals[2])
#                 if (mat and result) or mat is None:
#                     details = dict(zip(cols, vals))
#                     details['id'] = i
#                     items.append(Item(int(vals[3]), int(vals[4]), oid=vals[12], details=r))
#             # print('文件{0}一共有{1}个item'.format(file_name, counter - 4))
#     return items, mat_names
def load_pieces_from_excels_dir(mat: str, csv_dir: str) -> List[Item]:
    mat_names = set()
    if not csv_dir:
        csv_dir = "./csv"
    items = []
    cols = '优化批次号,部件条码,材料描述,开料长,开料宽,部件数量,超产,纹理,部件名称,部件规格,材料编码,备注,订单号,批次号,配套批号,分单号,开槽线,异形,CNC加工代码,CNC反面加工代码,套数,第几套,预包装信息,总包数,第几包,第几层,单元序号,单元名称,单元规格'.split(
        ',')
    i = 0
    for file_name in os.listdir(csv_dir):
        file_path = os.path.join(csv_dir, file_name)
        if not file_path.endswith('.csv'):
            continue
        with open(file_path) as f:
            counter = 0
            for r in f:
                if counter == 0:
                    counter += 1
                    continue
                if counter == 1:
                    counter += 1
                if counter == 2 or counter == 3:
                    counter += 1
                    continue

                i += 1
                vals = r.split(',')[:29]
                mat_names.add(vals[2])
                if (mat and vals[2] == mat) or mat is None:
                    details = dict(zip(cols, vals))
                    details['id'] = i
                    items.append(Item(max(int (vals[3]), int (vals[4])), min(int (vals[3]), int (vals[4])), oid=vals[12], details=r))
    return items, mat_names




def load_pieces_from_excels_dir(mat: str, csv_dir: str) -> List[Item]:
    mat_names = set()
    if not csv_dir:
        csv_dir = "./csv"
    items = []
    cols = '优化批次号,部件条码,材料描述,开料长,开料宽,部件数量,超产,纹理,部件名称,部件规格,材料编码,备注,订单号,批次号,配套批号,分单号,开槽线,异形,CNC加工代码,CNC反面加工代码,套数,第几套,预包装信息,总包数,第几包,第几层,单元序号,单元名称,单元规格'.split(
        ',')
    i = 0
    for file_name in os.listdir(csv_dir):
        file_path = os.path.join(csv_dir, file_name)
        if not file_path.endswith('.csv'):
            continue
        with open(file_path) as f:
            counter = 0
            for r in f:
                if counter == 0:
                    counter += 1
                    continue
                if counter == 1:
                    counter += 1
                if counter == 2 or counter == 3:
                    counter += 1
                    continue

                i += 1
                vals = r.split(',')[:29]
                mat_names.add(vals[2])
                if (mat and vals[2] == mat) or mat is None:
                    details = dict(zip(cols, vals))
                    details['id'] = i
                    items.append(Item(int(vals[3]), int((vals[4])), oid=vals[12], details=r))
    return items, mat_names



# def load_pieces_from_database(mat,data) -> List[Item]:
#     mat_names = set()
#     config = dict(host='localhost', user='root', password='000000',database="jinpai",
#                  cursorclass=pymysql.cursors.DictCursor
#                  )
#     # 建立连接
#     conn = pymysql.Connect(**config)
#     # 自动确认commit True
#     conn.autocommit(1)
#     # 设置光标
#     cursor = conn.cursor()
#     sql = "SELECT * FROM kailiao"
#     i=0
#     items=[]
#
#     try:
#        # 执行SQL语句
#         cursor.execute(sql)
#        # print('执行成功')
#        # 获取所有记录列表
#         results = cursor.fetchall()
#         # print(results)
#         cols=cursor.description
#         for row in results:
#             # print(row)
#
#             mat_names.add(row['mat_name'])
#             # if (mat and row['mat_name'] == mat) or mat is None:
#
#             items.append(Item(int(row['length']), int(row['width']), oid=row['order_id'], details=row['other_detail']))
#     except:
#        print ("Error: unable to fecth data")
#
#     # 关闭数据库连接
#     cursor.close()
#     conn.close()
#     print(mat_names)
#     print(items)
#     return items, mat_names

